using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Web;
using MySql.Data.MySqlClient;
namespace LdExpress
{
    /// <summary>
    /// 
    /// </summary>
    public class LocalCharge
    {
        public long ID = 0;
        public long CarrID = 0;
        public long PortID = 0;
        public string Note = "";
        public DateTime LastUpdate = DateTime.Now;
        public const int BuyPriceType = 1;
        public const int SellPriceNoVatType = 2;
        public const int SellPriceWithVatType = 3;
        public LocalCharge()
        {
        }
        public LocalCharge(long lID)
        {
            this.ID = lID;
        }
        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "";
            if (ID <= 0) // new object, we insert new record to database
            {
                //ID = long.Parse(DBModule.GetNewID(1, typeof(LocalCharge), "tbl_LocalCharge", "ID", "ID", con, trans));
                // build SQL statement
                sqlCommand = "Insert into tbl_LocalCharge (" +
                    //"ID," +
                             "CarrID," +
                             "PortID," +
                             "Note," +
                             "LastUpdate) " +
                         " values(" +
                    //ID.ToString() + "," +
                             CarrID.ToString() + "," +
                             PortID.ToString() + "," +
                             "'" + DBModule.RefineString(Note) + "'," +
                              DBModule.RefineDatetime(LastUpdate) + ")";
                // run SQL statement
                DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
                string tempID = DBModule.ExecuteQueryForOneResult("Select Max(ID) from tbl_LocalCharge", cn, trans);
                this.ID = long.Parse(tempID);
                UserObjectsLog.SaveObjectActionLog("LocalCharges", "add", cn, trans);                
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                sqlCommand = "Update tbl_LocalCharge set " +
                    //"ID=" + ID.ToString() + "," +
                             "CarrID=" + CarrID.ToString() + "," +
                             "PortID=" + PortID.ToString() + "," +
                             "Note='" + DBModule.RefineString(Note) + "'," +
                             "LastUpdate=" + DBModule.RefineDatetime(LastUpdate) + "" +
                    " where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
                UserObjectsLog.SaveObjectActionLog("LocalCharges", "edit", cn, trans);                
            }
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {
            //Buil SQL Statement
            //Run SQL Statement
            string sqlCommand = "DELETE FROM tbl_LocalChargeDetail WHERE LocalChargeID = " + this.ID.ToString() + " ;";
            sqlCommand += "DELETE FROM tbl_LocalCharge WHERE ID = " + this.ID.ToString() + " limit 1";
            DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
            UserObjectsLog.SaveObjectActionLog("LocalCharges", "delete", cn, trans);
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "";
            //Build SQL Statement
            //Run SQL Statement
            sqlCommand = "SELECT * FROM tbl_LocalCharge WHERE ID = " + ID.ToString() + " limit 0,1";
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID"))
                    ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("CarrID"))
                    CarrID = long.Parse(dr["CarrID"].ToString());
                if (!dr.IsNull("PortID"))
                    PortID = long.Parse(dr["PortID"].ToString());
                if (!dr.IsNull("Note"))
                    Note = dr["Note"].ToString();
                if (!dr.IsNull("LastUpdate"))
                    LastUpdate = DateTime.Parse(dr["LastUpdate"].ToString());
            }
            else
            {
            }
        }
        public static void GetList(string Condition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string sqlCommand = "";
            ds = null;
            // build SQL statement
            sqlCommand = "Select tbl_LocalCharge.*,tbl_carrs.Name, tbl_ports.Name from tbl_LocalCharge " +
                " left join tbl_carrs on tbl_LocalCharge.CarrID=tbl_carrs.ID " +
                " left join tbl_ports on tbl_LocalCharge.PortID=tbl_ports.ID " +
                " where 1=1 ";
            if ((Condition != "") && (Condition != null))
            {
                sqlCommand += Condition;
            }

            if ((OrderBy != null) && (OrderBy != ""))
                sqlCommand = sqlCommand + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(sqlCommand, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(sqlCommand, cn, trans);
        }

        public static void GetList(string Condition, int LocalChargeType, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string sqlCommand = "";
            ds = null;
            // build SQL statement
            string mapColumn = "BuyPrice";
            if (LocalChargeType == 1)
                mapColumn = "BuyPrice";
            if (LocalChargeType == 2)
                mapColumn = "SellPriceNoVat";
            else if (LocalChargeType == 3)
                mapColumn = "SellPriceWithVat";

            sqlCommand = "SELECT tbl_localCharge.*,tbl_carrs.Name," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'EXPORTATION_OTHC_20DC', " + mapColumn + ", NULL)) AS CHAR) AS 'EXPORTATION_OTHC_20DC'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'EXPORTATION_OTHC_40HC', " + mapColumn + ", NULL)) AS CHAR) AS 'EXPORTATION_OTHC_40HC'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'EXPORTATION_OTHC_20RF', " + mapColumn + ", NULL)) AS CHAR) AS 'EXPORTATION_OTHC_20RF'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'EXPORTATION_OTHC_40RF', " + mapColumn + ", NULL)) AS CHAR) AS 'EXPORTATION_OTHC_40RF'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'EXPORTATION_SEAL_BOX', " + mapColumn + ", NULL)) AS CHAR) AS 'EXPORTATION_SEAL_BOX'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'EXPORTATION_DOC_SET', " + mapColumn + ", NULL)) AS CHAR) AS 'EXPORTATION_DOC_SET'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'EXPORTATION_TELEX_BL', " + mapColumn + ", NULL)) AS CHAR) AS 'EXPORTATION_TELEX_BL'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'EXPORTATION_MNF_BL', " + mapColumn + ", NULL)) AS CHAR) AS 'EXPORTATION_MNF_BL'," +
                //" CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'EXPORTATION_AMS_BL', " + mapColumn + ", NULL)) AS CHAR) AS 'EXPORTATION_AMS_BL'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'IMPORTATION_DTHC_20DC', " + mapColumn + ", NULL)) AS CHAR) AS 'IMPORTATION_DTHC_20DC'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'IMPORTATION_DTHC_40HC', " + mapColumn + ", NULL)) AS CHAR) AS 'IMPORTATION_DTHC_40HC'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'IMPORTATION_DTHC_20RF', " + mapColumn + ", NULL)) AS CHAR) AS 'IMPORTATION_DTHC_20RF'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'IMPORTATION_DTHC_40RF', " + mapColumn + ", NULL)) AS CHAR) AS 'IMPORTATION_DTHC_40RF'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'IMPORTATION_CONTCLEANING_20DC', " + mapColumn + ", NULL)) AS CHAR) AS 'IMPORTATION_CONTCLEANING_20DC'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'IMPORTATION_CONTCLEANING_40DC', " + mapColumn + ", NULL)) AS CHAR) AS 'IMPORTATION_CONTCLEANING_40DC'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'IMPORTATION_DO_BL', " + mapColumn + ", NULL)) AS CHAR) AS 'IMPORTATION_DO_BL'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'IMPORTATION_HL_BL', " + mapColumn + ", NULL)) AS CHAR) AS 'IMPORTATION_HL_BL'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'IMPEXP_EBS_20DC', " + mapColumn + ", NULL)) AS CHAR) AS 'IMPEXP_EBS_20DC'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'IMPEXP_EBS_40HC', " + mapColumn + ", NULL)) AS CHAR) AS 'IMPEXP_EBS_40HC'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'IMPEXP_CIC_20DC', " + mapColumn + ", NULL)) AS CHAR) AS 'IMPEXP_CIC_20DC'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'IMPEXP_CIC_40HC', " + mapColumn + ", NULL)) AS CHAR) AS 'IMPEXP_CIC_40HC'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'LPF_DAYS', " + mapColumn + ", NULL)) AS CHAR) AS 'LPF_DAYS'," +
            " CAST(GROUP_CONCAT(IF(tbl_localChargeDetail.Code = 'LPF_FEE', " + mapColumn + ", NULL)) AS CHAR) AS 'LPF_FEE'" +
            " FROM tbl_localCharge INNER JOIN tbl_carrs ON tbl_localCharge.CarrID=tbl_carrs.ID" +
            " LEFT JOIN tbl_localChargeDetail ON tbl_localCharge.ID=tbl_LocalChargeDetail.LocalChargeID WHERE 1=1 ";
            if ((Condition != "") && (Condition != null))
            {
                sqlCommand += Condition;
            }

            sqlCommand += " GROUP BY tbl_localCharge.ID";
            if ((OrderBy != null) && (OrderBy != ""))
                sqlCommand = sqlCommand + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(sqlCommand, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(sqlCommand, cn, trans);
        }

        public static void DoSearch(string keywords, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "";
            ds = null;
            // build SQL statement
            sqlCommand = "Select `ID` from tbl_LocalCharge where 1=1 ";

            if ((OrderBy != null) && (OrderBy != ""))
                sqlCommand = sqlCommand + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(sqlCommand, cn, trans);
        }
        public static int GetLPFDaysByCarrName(string CarrName, MySqlConnection cn, MySqlTransaction trans)
        {
            int retVal = 0;
            string sqlCommand = "SELECT tbl_localChargeDetail.BuyPrice " +  //tbl_localChargeDetail.*,tbl_carrs.Name" +
             " FROM tbl_localCharge INNER JOIN tbl_carrs ON tbl_localCharge.CarrID=tbl_carrs.ID" +
             " LEFT JOIN tbl_localChargeDetail ON tbl_localCharge.ID=tbl_LocalChargeDetail.LocalChargeID WHERE 1=1 " +
             " AND tbl_carrs.Name='" + DBModule.RefineString(CarrName) + "' AND tbl_localChargeDetail.Code='LPF_DAYS'";
            string result = DBModule.ExecuteQueryForOneResult(sqlCommand, cn, trans);
            if (!string.IsNullOrEmpty(result))
            {
                try
                {
                    retVal = int.Parse(result);
                }
                catch { }
            }
            return retVal;
        }
    }
}
